package com.szholly.pro.watergas_server.ps.service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.RowBase;
import com.szholly.plug.form.excel.utils.StringUtil;
import com.szholly.pro.watergas_server.common.data.DataUtil;
import org.springframework.stereotype.Service;

/**
 * 质控水样比对
 * @author wzj
 *
 */
@Service
public class WaterThanService {
	
	
	/**
	 * 获取水样比对信息
	 * @param params
	 * @return
	 */
	public List<Object> queryWaterThan(Map<String, String> params){
		//公司
		String CompanySql="select t3.companycode,t3.companyname,count(t1.guid) as thanCount from qc_water_than t1" 
				+" left join qc_pointinfo t2 on t1.stationid=t2.pointcode"
				+"  left join BC_SITE_INFO t4 on t4.code = t2.pointcode"
				+" left join qc_companyinfo t3 on t3.COMPANYCODE=t2.companycode where 1=1";	
		//站点
		String StationSql=" select t2.pointcode,t4.sample_position,count(t1.guid) as thanCount,"
			+" t1.sampleid, case  t1.thantype  when '0' then '数据比对' when '1' then '盲样考核' when '2' then '抽测比对' end as thanname from qc_water_than t1" 
		       +" left join qc_pointinfo t2 on t1.stationid=t2.pointcode"
		       +"  left join BC_SITE_INFO t4 on t4.code=t2.pointcode"
		        +" left join qc_companyinfo t3 on t3.COMPANYCODE=t2.companycode where 1=1 ";
		String ThanSql="select t1.*,t6.monitorparamname,t3.companyname,t4.sample_position,t7.pmad  from qc_water_than t1" 
		       +" left join qc_pointinfo t2 on t1.stationid=t2.pointcode"
				+" left join BC_SITE_INFO t4 on t4.code = t2.pointcode"
		       +" left join qc_companyinfo t3 on t3.COMPANYCODE=t2.companycode"
		       +" left join  qc_monitorparam  t6 on t6.monitorparamcode=t1.parmname  and t6.monitortypecode=t2.pointtype"
				+" left join qc_monitorparamdetail t7 on t7.paramguid=t6.guid and t7.mcdownlimit<t1.value and t7.mcuplimit>t1.value where 1=1";
		String userSql="select t5.c_userealname,count(t1.guid) as thanCount,t4.sample_position  from qc_water_than t1"
	           +" left join qc_pointinfo t2 on t1.stationid=t2.pointcode"
				+"  left join BC_SITE_INFO t4 on t4.code = t2.pointcode"
	           +" left join qc_companyinfo t3 on t3.COMPANYCODE=t2.companycode"
	           +" left join data_defulatdomain t4 on t4.s_domaincode=t1.parmname and t4.s_domainname='深圳供水参数'"
	           +" left join safe_user t5 on t5.s_userid=t1.userid where 1=1";	          
	     if(StringUtil.IsNotNullOrEmpty(params.get("start"))){
	   		CompanySql+="  and t1.datetime>=to_date('"+params.get("start")+" 00:00:00','yyyy-mm-dd hh24:mi:ss')";
	   		StationSql+="  and t1.datetime>=to_date('"+params.get("start")+" 00:00:00','yyyy-mm-dd hh24:mi:ss')";
	   		ThanSql+="  and t1.datetime>=to_date('"+params.get("start")+" 00:00:00','yyyy-mm-dd hh24:mi:ss')";
	   		userSql+="  and t1.datetime>=to_date('"+params.get("start")+" 00:00:00','yyyy-mm-dd hh24:mi:ss')";
	   	}
	   	if(StringUtil.IsNotNullOrEmpty(params.get("end"))){
	   		CompanySql+="  and t1.datetime<=to_date('"+params.get("end")+" 23:59:59','yyyy-mm-dd hh24:mi:ss')";
	   		StationSql+="  and t1.datetime<=to_date('"+params.get("end")+" 23:59:59','yyyy-mm-dd hh24:mi:ss')";
	   		ThanSql+="  and t1.datetime<=to_date('"+params.get("end")+" 23:59:59','yyyy-mm-dd hh24:mi:ss')";
	   		userSql+="  and t1.datetime<=to_date('"+params.get("end")+" 23:59:59','yyyy-mm-dd hh24:mi:ss')";
	   	}
	   	if(StringUtil.IsNotNullOrEmpty(params.get("stationid"))){
	   		CompanySql+="  and t1.stationid='"+params.get("stationid")+"'";
	   		StationSql+="  and t1.stationid='"+params.get("stationid")+"'";
	   		ThanSql+="  and t1.stationid='"+params.get("stationid")+"'";
	   		userSql+="  and t1.stationid='"+params.get("stationid")+"'";
	   	}
		if(StringUtil.IsNotNullOrEmpty(params.get("thanType"))){
	   		CompanySql+="  and t1.thantype in("+params.get("thanType")+")";
	   		StationSql+="  and t1.thantype in("+params.get("thanType")+")";
	   		ThanSql+="  and t1.thantype in("+params.get("thanType")+")";
	   		userSql+="  and t1.thantype in ("+params.get("thanType")+")";
	   	}
	   	CompanySql+=" group by t3.companycode,t3.companyname order by t3.companyname asc";
	   	StationSql+=" group by t2.pointcode,t4.sample_position,t3.companyname,t1.sampleid,t1.thantype order by t3.companyname,t4.sample_position asc";
	   	ThanSql+=" order by t3.companyname,t4.sample_position,t1.datetime asc";
	    userSql +=" group by t3.companyname,t4.sample_position,t2.pointname,t5.c_userealname order by t3.companyname,t4.sample_position asc";
		List<GlobalParameter> listParam = new ArrayList<>();
		List<RowBase> CompanyList= DataUtil.getSelectRows(CompanySql, listParam);//公司
		List<RowBase> stationList=DataUtil.getSelectRows(StationSql, listParam);//获取站点
		List<RowBase> ThanList=DataUtil.getSelectRows(ThanSql, listParam);//获取比对数据
		List<RowBase> userList=DataUtil.getSelectRows(userSql, listParam);//获取比对人员
		List<Object> dataList=new ArrayList<>();
		HashMap<Object,Object> data=new HashMap<>();
		data.put("CompanyList", CompanyList);
		data.put("stationList", stationList);
		data.put("ThanList", ThanList);
		data.put("userList", userList);
		dataList.add(data);
		return dataList;
	}

}
